/**
 *
 * Copyright (c) 2011 TLOSS. All rights reserved.
 * Created on Apr 22, 2011
 *
 */
package com.tloss.database.db2obj.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tloss.database.db2obj.AbstractSQLExtractror;
import com.tloss.database.db2obj.Column;
import com.tloss.database.db2obj.SQLExtractor;
import com.tloss.database.db2obj.Table;
import com.tloss.database.db2obj.TypeExtractor;

/**
 * @author Tran Tung
 * 
 */
public class MysqlSQLExtractor extends AbstractSQLExtractror implements
		SQLExtractor {
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	public List<Table> extractTable() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		PreparedStatement subPreparedStatement = null;
		ResultSet subResultSet = null;
		ArrayList<Table> result = new ArrayList<Table>();
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection
					.prepareStatement("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ?");
			preparedStatement.setString(1, schema);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Table table = new Table();
				table.setName(resultSet.getString("TABLE_NAME"));
				result.add(table);
			}
			for (Table table : result) {
				subPreparedStatement = connection
						.prepareStatement("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = ? AND table_schema = ?");
				subPreparedStatement.setString(1, table.getName());
				subPreparedStatement.setString(2,schema);
				subResultSet =  subPreparedStatement.executeQuery();
				ArrayList<Column> columns =  new ArrayList<Column>();
				table.setColumns(columns);
				while (subResultSet.next()) {
					Column column =  new Column();
					columns.add(column);
					column.setName(subResultSet.getString("column_name"));
					column.setColumnType(subResultSet.getString("column_type"));
					column.setDataType(subResultSet.getString("data_type"));
					column.setIsNullable(subResultSet.getString("is_nullable"));
					column.setCharacterMaximumLength(subResultSet.getInt("character_maximum_length"));
					column.setNumericPrecision(subResultSet.getInt("numeric_precision"));
					column.setNumericScale(subResultSet.getInt("numeric_scale"));
				}
				subPreparedStatement.close();
				subResultSet.close();
			}
		} catch (SQLException e) {
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
			} catch (SQLException e) {
				logger.error("", e);
			}
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error("", e);
			}

		}
		return result;
	}

	public TypeExtractor getTypeExtractor() {
		return new MysqlTypeExtractor();
	}

}
